﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

using AKMII.DMRA.Common;

namespace AKMII.DMRA.DataAccess
{
    public class UserAccessor
    {
        /// <summary>
        /// Add new user.
        /// </summary>
        /// <param name="user">Entity class of user.</param>
        /// <returns></returns>
        public bool AddUser(User user)
        {
            const string SpName = "sp_Insert_Account";

            try
            {
                object[] parameters = new object[]
                {
                    user.Id,
                    user.FirstName,
                    user.LastName,
                    user.Password,
                    user.Email,
                    Convert.ToInt32(user.Type),
                    user.DivisionNum
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in add user", ex);
            }

            return true;
        }

        /// <summary>
        /// Delete exist user.
        /// </summary>
        /// <param name="id">User id.</param>
        /// <returns></returns>
        public bool DeleteUser(string id)
        {
            const string SpName = "sp_Delete_Account";

            try
            {
                object[] parameters = new object[]
                {
                    id
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in delete user", ex);
            }

            return true;
        }

        /// <summary>
        /// Enable or disable user.
        /// </summary>
        /// <param name="id">User id.</param>
        /// <param name="status">User status, active or inactive.</param>
        /// <returns></returns>
        public bool UpdateUserStatusById(string id, int status)
        {
            const string SpName = "sp_Update_AccountStatus";

            try
            {
                object[] parameters = new object[]
                {
                    id,
                    status
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in update user status", ex);
            }

            return true;
        }

        /// <summary>
        /// Change user password.
        /// </summary>
        /// <param name="id">User id.</param>
        /// <param name="newPassword">New password.</param>
        /// <param name="oldPassword">Old password.</param>
        /// <returns></returns>
        public bool UpdateUserPasswordById(string id, string newPassword)
        {
            const string SpName = "sp_Update_Password";

            try
            {
                object[] parameters = new object[]
                {
                    id,
                    newPassword
                };

                object o = SqlHelper.ExecuteScalar(Constants.ConnectionString, SpName, parameters);
                if (o != null)
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in update user password", ex);
            }

            return true;
        }

        /// <summary>
        /// Get all MD summary information.
        /// </summary>
        /// <returns></returns>
        public List<MarketDirector> GetMarketDirectors(int year)
        {
            List<MarketDirector> marketDirectors = new List<MarketDirector>();
            const string SpName = "sp_Get_AccountsHistory";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { year };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    MarketDirector marketDirector = new MarketDirector();

                    marketDirector.Id = !reader.IsDBNull(0) ? reader.GetString(0) : null;
                    marketDirector.TotalAgents = reader.GetInt32(1);
                    marketDirector.TotalZipCodes = reader.GetInt32(2);
                    marketDirector.IsOrderInJan = Convert.ToBoolean(reader.GetInt32(3));
                    marketDirector.IsOrderInFeb = Convert.ToBoolean(reader.GetInt32(4));
                    marketDirector.IsOrderInMar = Convert.ToBoolean(reader.GetInt32(5));
                    marketDirector.IsOrderInApr = Convert.ToBoolean(reader.GetInt32(6));
                    marketDirector.IsOrderInMay = Convert.ToBoolean(reader.GetInt32(7));
                    marketDirector.IsOrderInJun = Convert.ToBoolean(reader.GetInt32(8));
                    marketDirector.IsOrderInJul = Convert.ToBoolean(reader.GetInt32(9));
                    marketDirector.IsOrderInAug = Convert.ToBoolean(reader.GetInt32(10));
                    marketDirector.IsOrderInSep = Convert.ToBoolean(reader.GetInt32(11));
                    marketDirector.IsOrderInOct = Convert.ToBoolean(reader.GetInt32(12));
                    marketDirector.IsOrderInNov = Convert.ToBoolean(reader.GetInt32(13));
                    marketDirector.IsOrderInDec = Convert.ToBoolean(reader.GetInt32(14));
                    marketDirector.FullName = !reader.IsDBNull(15) ? reader.GetString(15) : null;

                    marketDirectors.Add(marketDirector);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get market directors", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return marketDirectors;
        }

        public List<MarketDirector> GetMarketDirectors(int year, int month)
        {
            List<MarketDirector> marketDirectors = new List<MarketDirector>();
            const string SpName = "sp_Get_MdSummary";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { year.ToString(), month.ToString() };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    MarketDirector marketDirector = new MarketDirector();

                    marketDirector.Id = !reader.IsDBNull(0) ? reader.GetString(0) : null;
                    marketDirector.ActiveLeadCount = reader.GetInt32(1);
                    marketDirector.LapseLeadCount = reader.GetInt32(2);
                    marketDirector.BusinessLeadCount = reader.GetInt32(3);
                    marketDirector.NeighborLeadCount = reader.GetInt32(4);

                    marketDirectors.Add(marketDirector);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get market director order summary", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return marketDirectors;
        }

        public User GetUserById(string id)
        {
            User user = null;
            const string SpName = "sp_Get_Account";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { id };
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                if (reader.Read())
                {
                    user = new User();
                    user.Id = !reader.IsDBNull(0) ? reader.GetString(0) : null;
                    user.FirstName = !reader.IsDBNull(1) ? reader.GetString(1) : null;
                    user.LastName = !reader.IsDBNull(2) ? reader.GetString(2) : null;
                    user.Password = !reader.IsDBNull(3) ? reader.GetString(3) : null;
                    user.Email = !reader.IsDBNull(4) ? reader.GetString(4) : null;
                    user.Type = (UserType)reader.GetInt32(5);
                    user.Status = (UserStatus)reader.GetInt32(6);
                    user.LastLoginTime = !reader.IsDBNull(7) ? reader.GetDateTime(7) : (DateTime?)null;
                    user.ForceChangePassword = reader.GetBoolean(8);
                    user.FailedLoginTimes = reader.GetInt32(9);
                    user.GraceLoginTimes = reader.GetInt32(10);
                    user.IsRuleEnabled = reader.GetBoolean(13);
                    user.IsRepeatRuleEnabled = reader.GetBoolean(14);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get account by id", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return user;
        }

        public bool CheckUser(string id, string mailAddress)
        {
            const string SpName = "sp_Check_User";

            try
            {
                object[] parameters = new object[] { id, mailAddress };
                object o = SqlHelper.ExecuteScalar(Constants.ConnectionString, SpName, parameters);

                if (o != null)
                {
                    return true;
                }

            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get account by id", ex);
            }

            return false;
        }

        public bool UpdateUser(User user)
        {
            const string SpName = "sp_Update_Account";

            try
            {
                object[] parameters = new object[]
                {
                    user.Id,
                    user.FirstName,
                    user.LastName,
                    user.Email,
                    user.Type,
                    user.Status,
                    user.IsRuleEnabled,
                    user.IsRepeatRuleEnabled
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in update user", ex);
            }

            return true;
        }

        public List<User> GetAllUsers(int type)
        {
            const string SpName = "sp_Get_Accounts";
            List<User> allUser = new List<User>();
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { type, null };
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    User user = new User();

                    user = new User();
                    user.Id = !reader.IsDBNull(0) ? reader.GetString(0) : null;
                    user.FirstName = !reader.IsDBNull(1) ? reader.GetString(1) : null;
                    user.LastName = !reader.IsDBNull(2) ? reader.GetString(2) : null;
                    user.Password = !reader.IsDBNull(3) ? reader.GetString(3) : null;
                    user.Email = !reader.IsDBNull(4) ? reader.GetString(4) : null;
                    user.Type = (UserType)reader.GetInt32(5);
                    user.Status = (UserStatus)reader.GetInt32(6);
                    user.LastLoginTime = !reader.IsDBNull(7) ? reader.GetDateTime(7) : (DateTime?)null;
                    user.ForceChangePassword = reader.GetBoolean(8);
                    user.FailedLoginTimes = reader.GetInt32(9);
                    user.GraceLoginTimes = reader.GetInt32(10);
                    user.IsRuleEnabled = reader.GetBoolean(13);
                    user.IsRepeatRuleEnabled = reader.GetBoolean(14);

                    allUser.Add(user);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get all accounts", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return allUser;
        }

        public List<User> GetAllUsers(int type, int status)
        {
            const string SpName = "sp_Get_Accounts";
            List<User> allUser = new List<User>();
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { type, status };
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    User user = new User();

                    user = new User();
                    user.Id = !reader.IsDBNull(0) ? reader.GetString(0) : null;
                    user.FirstName = !reader.IsDBNull(1) ? reader.GetString(1) : null;
                    user.LastName = !reader.IsDBNull(2) ? reader.GetString(2) : null;
                    user.Password = !reader.IsDBNull(3) ? reader.GetString(3) : null;
                    user.Email = !reader.IsDBNull(4) ? reader.GetString(4) : null;
                    user.Type = (UserType)reader.GetInt32(5);
                    user.Status = (UserStatus)reader.GetInt32(6);
                    user.LastLoginTime = !reader.IsDBNull(7) ? reader.GetDateTime(7) : (DateTime?)null;
                    user.ForceChangePassword = reader.GetBoolean(8);
                    user.FailedLoginTimes = reader.GetInt32(9);
                    user.GraceLoginTimes = reader.GetInt32(10);
                    user.IsRuleEnabled = reader.GetBoolean(13);
                    user.IsRepeatRuleEnabled = reader.GetBoolean(14);

                    allUser.Add(user);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get all accounts", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return allUser;
        }

        public bool UpdateLoginFailedTimes(string id, int times)
        {
            const string SpName = "sp_Update_LoginFailedTimes";

            try
            {
                object[] parameters = new object[] { id, times };
                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in update failed login times by id", ex);
            }

            return true;
        }

        public bool UpdateGraceLoginTimes(string id, int times)
        {
            const string SpName = "sp_Update_GraceLoginTimes";

            try
            {
                object[] parameters = new object[] { id, times };
                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in update grace login times by id", ex);
            }

            return true;
        }

        public bool UpdateAgentNumberByUserId(string userId, string division, string market, int agentNum)
        {
            const string SpName = "sp_Update_UpdateAgentNumberByUserId";

            try
            {
                object[] parameters = new object[] { userId, division, market, agentNum };
                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in update agentNum by userId/division/market ", ex);
            }

            return true;
        }

        public bool UpdateUserForLoginSuccess(string id, DateTime datetime, int agentNumber)
        {
            const string SpName = "sp_Update_LoginSuccess";

            try
            {
                object[] parameters = new object[] { id, datetime, agentNumber };
                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in update login success by id", ex);
            }

            return true;
        }

        public bool ResetUser(string id, string password)
        {
            const string SpName = "sp_Reset_User";

            try
            {
                object[] parameters = new object[] { id, password };
                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in reset user", ex);
            }

            return true;
        }

        public DateTime? GetLastMoifiedPasswordTime(string id)
        {
            const string SpName = "sp_Get_PassowrdHistory";
            DateTime? datetime = null;
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { id };
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);
                if (reader.Read())
                {
                    if (!Convert.IsDBNull(reader["P1Date"]))
                        datetime = Convert.ToDateTime(reader["P1Date"]);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in reset user", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return datetime;
        }

        public bool IsPasswordUsed(string id, string password)
        {
            const string SpName = "sp_Get_PassowrdHistory";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { id };
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);
                if (reader.Read())
                {
                    if (!Convert.IsDBNull(reader["PasswordP1"]) && password == reader["PasswordP1"].ToString())
                        return true;
                    else if (!Convert.IsDBNull(reader["PasswordP2"]) && password == reader["PasswordP2"].ToString())
                        return true;
                    else if (!Convert.IsDBNull(reader["PasswordP3"]) && password == reader["PasswordP3"].ToString())
                        return true;
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in IsPasswordUsed", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return false;
        }

        public string GetMailById(string id)
        {
            const string SpName = "sp_Get_MailAddress";
            string mailAddress = string.Empty;

            try
            {
                object[] parameters = new object[] { id };
                object o = SqlHelper.ExecuteScalar(Constants.ConnectionString, SpName, parameters);
                if (o != null)
                {
                    mailAddress = o.ToString();
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get mail address", ex);
            }

            return mailAddress;
        }

        public string GetMailForDivisionAdmin(string id)
        {
            const string SpName = "sp_Get_MailForDivision";
            string mailAddress = string.Empty;

            try
            {
                object[] parameters = new object[] { id };
                object o = SqlHelper.ExecuteScalar(Constants.ConnectionString, SpName, parameters);
                if (o != null)
                {
                    mailAddress = o.ToString();
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get mail address for division admin", ex);
            }

            return mailAddress;
        }

        public List<string> GetMailAddress(int month, int year, UserType type)
        {
            const string SpName = "sp_Get_AccountWithoutOrder";
            List<string> addresses = new List<string>();
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { year.ToString(), month.ToString(), Convert.ToInt32(type) };
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    if (!reader.IsDBNull(0))
                    {
                        addresses.Add(reader.GetString(0));
                    }
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get mail addresses", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return addresses;
        }

        public List<string> GetMailAddress(UserType type)
        {
            const string SpName = "sp_Get_MailAddresses";
            List<string> addresses = new List<string>();
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { Convert.ToInt32(type) };
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    if (!reader.IsDBNull(0))
                    {
                        addresses.Add(reader.GetString(0));
                    }
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get mail addresses", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return addresses;
        }

        public List<string> GetNames(int month, int year, UserType type, string DivisionNum)
        {
            const string SpName = "sp_Get_AccountWithoutOrder_ByDivision";
            List<string> names = new List<string>();
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { year.ToString(), month.ToString(), Convert.ToInt32(type), DivisionNum };
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    if (!reader.IsDBNull(0))
                    {
                        string accountID = reader[0].ToString();
                        names.Add(accountID);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get md names without order", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return names;
        }

        public List<string> GetNames(int month, int year)
        {
            const string SpName = "sp_Get_AccountWithOrder";
            List<string> names = new List<string>();
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { year.ToString(), month.ToString() };
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    if (!reader.IsDBNull(0))
                    {
                        names.Add(reader.GetString(0));
                    }
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get md names with order", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return names;
        }

        public int GetAccountTypeByID(string AccountID)
        {
            const string SpName = "sp_Get_AccountType";
            int AccountType = 1000;
            try
            {
                object[] parameters = new object[] { AccountID };
                object o = SqlHelper.ExecuteScalar(Constants.ConnectionString, SpName, parameters);
                if (o != null)
                {
                    AccountType = (int)o;
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in getAccountType", ex);
            }
            return AccountType;
        }

        public List<DivisionAdmin> GetAllDivisionAdmins()
        {
            const string SpName = "sp_Get_DivisionAdmin";
            List<DivisionAdmin> ListDivision = new List<DivisionAdmin>();
            SqlDataReader reader = null;
            object[] parameters = new object[] { };
            try
            {
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);
                while (reader.Read())
                {
                    DivisionAdmin Division = new DivisionAdmin();
                    Division.DivisionNum = reader[0].ToString();
                    Division.AccountID = reader[1].ToString();
                    Division.DivisionalAdminName = reader[2].ToString().Trim();
                    Division.EMail = reader[3].ToString();
                    ListDivision.Add(Division);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get division admin accounts", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
            }
            return ListDivision;
        }

        public List<MarketDirector> GetDivisionAdmins()
        {
            List<MarketDirector> marketDirectors = new List<MarketDirector>();
            const string SpName = "sp_Get_DivisionAdminWithLeads";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    MarketDirector marketDirector = new MarketDirector();

                    marketDirector.Email = !reader.IsDBNull(1) ? reader.GetString(1) : null;

                    marketDirectors.Add(marketDirector);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get division admins with leads assigned", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return marketDirectors;
        }

        public bool AddAgent(Agent agent)
        {
            const string SpName = "sp_Insert_Agent";

            try
            {
                object[] parameters = new object[]
                {
                    agent.Name,
                    null,
                    null,
                    agent.MdNumber
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in add agent", ex);
            }

            return true;
        }

        public Assignment GetAssignment(string mdNumber, int agentId)
        {
            Assignment assignment = null;
            const string SpName = "sp_Get_Agent";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { mdNumber, agentId, null, null };
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                if (reader.Read())
                {
                    assignment = new Assignment();

                    assignment.AgentId = reader.GetInt32(0);
                    assignment.AgentName = !reader.IsDBNull(1) ? reader.GetString(1) : null;
                    assignment.ActiveContacts = reader.GetDouble(5);
                    assignment.ActiveAppointMade = reader.GetDouble(6);
                    assignment.ActiveAppointKept = reader.GetDouble(7);
                    assignment.ActivePresentation = reader.GetDouble(8);
                    assignment.ActiveAppointSale = reader.GetDouble(9);
                    assignment.LapsedContacts = reader.GetDouble(10);
                    assignment.LapsedAppointMade = reader.GetDouble(11);
                    assignment.LapsedAppointKept = reader.GetDouble(12);
                    assignment.LapsedPresentation = reader.GetDouble(13);
                    assignment.LapsedAppointSale = reader.GetDouble(14);
                    assignment.NeighborContacts = reader.GetDouble(15);
                    assignment.NeighborAppointMade = reader.GetDouble(16);
                    assignment.NeighborAppointKept = reader.GetDouble(17);
                    assignment.NeighborPresentation = reader.GetDouble(18);
                    assignment.NeighborAppointSale = reader.GetDouble(19);
                    assignment.BusinessContacts = reader.GetDouble(20);
                    assignment.BusinessAppointMade = reader.GetDouble(21);
                    assignment.BusinessAppointKept = reader.GetDouble(22);
                    assignment.BusinessPresentation = reader.GetDouble(23);
                    assignment.BusinessAppointSale = reader.GetDouble(24);
                    assignment.RefContacts = reader.GetDouble(25);
                    assignment.RefAppointMade = reader.GetDouble(26);
                    assignment.RefAppointKept = reader.GetDouble(27);
                    assignment.RefPresentation = reader.GetDouble(28);
                    assignment.RefAppointSale = reader.GetDouble(29);
                    assignment.TSystemContacts = reader.GetDouble(30);
                    assignment.TSystemAppointMade = reader.GetDouble(31);
                    assignment.TSystemAppointKept = reader.GetDouble(32);
                    assignment.TSystemPresentation = reader.GetDouble(33);
                    assignment.TSystemAppointSale = reader.GetDouble(34);
                    assignment.TargetMarketContacts = reader.GetDouble(35);
                    assignment.TargetMarketAppointMade = reader.GetDouble(36);
                    assignment.TargetMarketAppointKept = reader.GetDouble(37);
                    assignment.TargetMarketPresentation = reader.GetDouble(38);
                    assignment.TargetMarketAppointSale = reader.GetDouble(39);
                    assignment.FieldContacts = reader.GetDouble(40);
                    assignment.FieldAppointMade = reader.GetDouble(41);
                    assignment.FieldAppointKept = reader.GetDouble(42);
                    assignment.FieldPresentation = reader.GetDouble(43);
                    assignment.FieldAppointSale = reader.GetDouble(44);
                    assignment.AvgAPVperApp = reader.GetDouble(45);
                    assignment.ActiveLeadCount = reader.GetInt32(46);
                    assignment.LapsedLeadCount = reader.GetInt32(47);
                    assignment.NeighborLeadCount = reader.GetInt32(48);
                    assignment.BusinessLeadCount = reader.GetInt32(49);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get assignment", ex);
            }

            return assignment;
        }

        public bool UpdateAssignment(Assignment assignment)
        {
            const string SpName = "sp_Update_AgentRatio";

            try
            {
                object[] parameters = new object[] 
                {
                   assignment.AgentId
                   ,assignment.ActiveContacts
                   ,assignment.ActiveAppointMade
                   ,assignment.ActiveAppointKept
                   ,assignment.ActivePresentation
                   ,assignment.ActiveAppointSale
                   ,assignment.LapsedContacts
                   ,assignment.LapsedAppointMade
                   ,assignment.LapsedAppointKept
                   ,assignment.LapsedPresentation
                   ,assignment.LapsedAppointSale
                   ,assignment.NeighborContacts
                   ,assignment.NeighborAppointMade
                   ,assignment.NeighborAppointKept
                   ,assignment.NeighborPresentation
                   ,assignment.NeighborAppointSale
                   ,assignment.BusinessContacts
                   ,assignment.BusinessAppointMade
                   ,assignment.BusinessAppointKept
                   ,assignment.BusinessPresentation
                   ,assignment.BusinessAppointSale
                   ,assignment.RefContacts
                   ,assignment.RefAppointMade
                   ,assignment.RefAppointKept
                   ,assignment.RefPresentation
                   ,assignment.RefAppointSale
                   ,assignment.TSystemContacts
                   ,assignment.TSystemAppointMade
                   ,assignment.TSystemAppointKept
                   ,assignment.TSystemPresentation
                   ,assignment.TSystemAppointSale
                   ,assignment.TargetMarketContacts
                   ,assignment.TargetMarketAppointMade
                   ,assignment.TargetMarketAppointKept
                   ,assignment.TargetMarketPresentation
                   ,assignment.TargetMarketAppointSale
                   ,assignment.FieldContacts
                   ,assignment.FieldAppointMade
                   ,assignment.FieldAppointKept
                   ,assignment.FieldPresentation
                   ,assignment.FieldAppointSale
                   ,assignment.AvgAPVperApp
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in update agent ratio", ex);
            }

            return true;
        }

        public bool UpdateAgentRatio(int agentId)
        {
            const string SpName = "sp_Update_AgentRatio";

            try
            {
                object[] parameters = new object[] { agentId };
                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in update default agent ratio", ex);
            }

            return true;
        }

        public List<Agent> GetAgents(string mdNumber, bool? isOrder)
        {
            List<Agent> agents = new List<Agent>();
            const string SpName = "sp_Get_Agent";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { mdNumber, null, null, isOrder };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    Agent agent = new Agent();

                    agent.Id = reader.GetInt32(0);
                    agent.Name = !reader.IsDBNull(1) ? reader.GetString(1) : string.Empty;
                    agent.MdNumber = !reader.IsDBNull(2) ? reader.GetString(2) : string.Empty;
                    agent.Territory = !reader.IsDBNull(3) ? reader.GetString(3) : string.Empty;
                    agent.AgentCode = !reader.IsDBNull(4) ? reader.GetString(4) : string.Empty;
                    agent.Latitude = !reader.IsDBNull(5) ? reader.GetString(5) : string.Empty;
                    agent.Longitude = !reader.IsDBNull(6) ? reader.GetString(6) : string.Empty;

                    agents.Add(agent);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get agents by md", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return agents;
        }

        public Agent GetAgent(string mdNumber, string agentName)
        {
            Agent agent = new Agent();
            const string SpName = "sp_Get_Agent";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { mdNumber, null, agentName, null };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                if (reader.Read())
                {
                    agent.Id = reader.GetInt32(0);
                    agent.Name = !reader.IsDBNull(1) ? reader.GetString(1) : string.Empty;
                    agent.MdNumber = !reader.IsDBNull(2) ? reader.GetString(2) : string.Empty;
                    agent.Territory = !reader.IsDBNull(3) ? reader.GetString(3) : string.Empty;
                    agent.AgentCode = !reader.IsDBNull(4) ? reader.GetString(4) : string.Empty;
                    agent.Latitude = !reader.IsDBNull(5) ? reader.GetString(5) : string.Empty;
                    agent.Longitude = !reader.IsDBNull(6) ? reader.GetString(6) : string.Empty;
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get agent by name", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return agent;
        }

        public List<string> GetDivMktByUserId(string userId)
        {
            List<string> divMkts = new List<string>();
            const string SpName = "sp_Get_DivMktByUserId";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[] { userId };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    divMkts.Add(reader.GetString(0) + "," + reader.GetString(1));
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get Div/Mkt by md", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return divMkts;
        }
    }
}
